﻿-- =============================================
-- Author:		Dmitry Nazarov
-- Create date: 06.04.2011
-- Description:	Отчет Отгруженные изделия
-- =============================================
CREATE PROCEDURE [dbo].[report_shipped_products] 
	@p_date_from date,
	@p_date_to date,
	@p_nsi_organizarion_id int,
	@p_xml_products xml
AS
BEGIN
	SET NOCOUNT ON;

	declare @docHandle int
	EXEC sp_xml_preparedocument @docHandle OUTPUT, @p_xml_products

    SELECT pu.NAME as PRODUCT_NAME,  vi.DATE, o.SHORT_NAME as ORGANIZATION_NAME, COUNT(*) as PRODUCT_COUNT,
			vi.EXEC_NUMBER as TNN, c.NUMBER as CONTRACT_NUM
    FROM PRODUCT_UNIT as pu
	INNER JOIN VAT_INVOICE as vi ON vi.ID = pu.VAT_INVOICE_ID
	LEFT JOIN NSI_ORGANIZATION as o ON o.ID = vi.NSI_ORGANIZATION_ID
	INNER JOIN PID on PID.ID = vi.PID
	INNER JOIN CONTRACT as c ON c.PID = PID.ID
	WHERE pu.NSI_PRODUCT_STATE_ID = 4
	AND vi.DATE BETWEEN @p_date_from AND @p_date_to
	AND 
	(
		(@p_nsi_organizarion_id is null)
		OR
		(vi.NSI_ORGANIZATION_ID = @p_nsi_organizarion_id)
	)
	AND
	(
		(@p_xml_products is null)
		OR
		(
			pu.NSI_PRODUCT_ID IN ( SELECT x.ID FROM OPENXML(@docHandle, N'/products/product') WITH (id int) as x )
		)
	)
	GROUP BY  o.SHORT_NAME, c.NUMBER, vi.DATE, pu.NAME, vi.EXEC_NUMBER

	EXEC sp_xml_removedocument @docHandle
END